package com.efast.dmom.mgmt.service.mes.report.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.efast.cafe.commponent.query.service.impl.QueryServiceImpl;
import com.efast.cafe.framework.bean.QueryPageBase;
import com.efast.cafe.framework.jdbc.JdbcDao;
import com.efast.cafe.portal.bean.query.QueryBean;
import com.efast.cafe.portal.bean.query.QueryConditionBean;
import com.efast.cafe.portal.bean.query.QueryOpBean;
import com.efast.dmom.mgmt.dao.mes.report.ReportVehicleRetrospectDao;
import com.efast.dmom.mgmt.entity.report.ReportVehicleRetrospect;

//获取整车质量追溯信息
@Service("tw_bas_lot_consumpation_QT533-505")
public class ReportRetrospectInfoServiceImpl extends QueryServiceImpl{
	@Autowired
	private ReportVehicleRetrospectDao reportVehicleRetrospectDao;
	@Autowired
	@Qualifier("jdbcDao")
	private JdbcDao jdbcDao;
	
	@Override
	public Object queryList(QueryBean querys) {
		QueryPageBase result = new QueryPageBase();
		QueryConditionBean queryConditionBean = querys.getQueryConditionBean();
		String sql = "";
		String sql2 = "	SELECT\r\n" + 
				"		con.lot_name as lotName,\r\n" + 
				"		lot.vin as vin,\r\n" + 
				"		lot.self_number selfNumber,\r\n" + 
				"		con.consumed_part_no_version as consumedPartNoVersion,\r\n" + 
				"		con.opt_counter as optCounter,\r\n" + 
				"		con.company_code as companyCode,\r\n" + 
				"		part.part_no as partNo,\r\n" + 
				"	    part.part_name_c as partNameC,\r\n" + 
				"		con.consumed_part_sn as consumedPartSn,\r\n" + 
				"		con.consumed_qty as consumedQty,\r\n" + 
				"		con.comments as comments,\r\n" + 
				"		con.production_line as productionLine,\r\n" + 
				"		con.operation as operation,\r\n" + 
				"		con.route as route,\r\n" + 
				"		con.station_name as stationName,\r\n" + 
				"		date_format(con.create_time, '%Y-%m-%d %H:%i:%s' ) as createTime,\r\n" + 
				"		con.create_user as createUser,\r\n" + 
				"	  suppl.suppl_name_c as supplierName,\r\n" + 
				"	  SUBSTRING_INDEX(SUBSTRING_INDEX(con.consumed_part_sn,\"#\",-2),\"#\",1) as productionCode,\r\n" + 
				"	  CONCAT_WS(\"-\",IFNULL(ruleItem.barcode_length,\"null\"),IFNULL(ruleItem.start_pattern,\"null\"),IFNULL(ruleItem.end_pattern,\"null\"),IFNULL(ruleItem.contain_pattern,\"null\")) as rule,\r\n" + 
				"	  con.replace_consumed_part_sn as replaceConsumedPartSn,\r\n" + 
				"	  ppl.suppl_name_c as lastSupplierName,\r\n" + 
				"	  SUBSTRING_INDEX(SUBSTRING_INDEX(con.replace_consumed_part_sn,\"#\",-2),\"#\",1) AS replaceProductionCode, \r\n" + 
				"	  con.replace_number as replaceNumber \r\n" + 
				"		FROM tw_bas_lot_consumpation as con\r\n" + 
				"	left join tw_bas_lot lot on con.lot_id=lot.lot_id\r\n" + 
				"	left JOIN tm_bas_part as part on con.consumed_part_no_version = part.part_no_version\r\n" + 
				"	left join tm_bas_suppl as suppl on suppl.suppl_no = SUBSTRING_INDEX(SUBSTRING_INDEX(consumed_part_sn, \"#\" ,-3),\"#\",1) \r\n" + 
				"	LEFT JOIN tr_bas_product_config as tbpc on lot.route = tbpc.route_name and lot.part_no_version =tbpc.part_no_version and con.operation_id = tbpc.operation_id\r\n" + 
				"	LEFT JOIN tr_bas_product_cfg_bi as cfgbi on tbpc.product_config_id = cfgbi.product_config_id and cfgbi.bom_item_part_no_version = con.consumed_part_no_version\r\n" +
				"	LEFT JOIN qm_bas_barcode_rule_item as ruleItem on ruleItem.rule_id = cfgbi.rule_id\r\n" +
				"	left JOIN  tm_bas_suppl as ppl on ppl.suppl_no =SUBSTRING_INDEX(SUBSTRING_INDEX(con.replace_consumed_part_sn, \"#\" ,-3),\"#\",1)\r\n" +
				"		where con.lot_name  not like '%-A'  ";
		String selfLotName =""; 
		String vinLotName = "";
		String selfNumber = "";
		Integer start = 0;
		Integer length = 0;
		if(queryConditionBean.getStart() == null || queryConditionBean.getLength() == null) {
			start = 0;
			length =querys.getTemplate().getPagenation();
		}
	    start = queryConditionBean.getStart();
		length = queryConditionBean.getLength();
		List<QueryOpBean> list = queryConditionBean.getQueryOpBeans();
		for(int i=0;i<list.size();i++) {
			String key = list.get(i).getKey().trim();
			String value = list.get(i).getValue().trim();
			if(key!=null &&!"".equals(key)&& value!=null && !"".equals(value) ) {
				if(key.equals("lot.vin")) {
					//获取车辆合拼信息
					List<ReportVehicleRetrospect> spliceInfo = reportVehicleRetrospectDao.getSpliceInfo(value);
					if(spliceInfo!=null && spliceInfo.size()>0) {
						for (int j=0;j<spliceInfo.size();j++) {
							
							//说明为货车合拼之后的查询
							 vinLotName = spliceInfo.get(j).getLotName();						
							 selfNumber = spliceInfo.get(j).getSelfNumber();
							 List<ReportVehicleRetrospect> selfLotNameList = reportVehicleRetrospectDao.getSelfLotName(selfNumber);
							 if(selfLotNameList!=null &&selfLotNameList.size()>0) {
								 selfLotName= selfLotNameList.get(0).getLotName();
							 }
							 if(j==0) {
								 sql+="  con.lot_name like '%"+vinLotName+"%' or con.lot_name like '%"+selfLotName+"%' ";
							 }else {
								 sql+=" or con.lot_name like '%"+vinLotName+"%' or con.lot_name like '%"+selfLotName+"%' ";
							 }
							 
						}
						sql="and ("+sql+"or lot.vin like '%"+value+"%' or lot.self_number like '%"+value+"%' )";
					}else {
						sql+="and (lot.vin like '%"+value+"%' or lot.self_number like '%"+value+"%' )";
					}
					
				}else if(key.equals("con.create_time")) {
					if(value!=null &&value.length()>0) {
						String[] split = value.split("~");
						if(split!=null &&split.length>1) {
							String beginTime = split[0];
							String endTime = split[1];
							sql+="and "+key+" between '"+beginTime+"' and '"+endTime+"'";
						}
						
					}
					
				}else {
					sql+="and "+key+" like '%"+value+"%'";
				}
			}
		}
		sql = sql2+sql;
		sql+="GROUP BY con.create_time,con.consumed_part_sn\r\n";
		System.out.println(sql);
		int count = jdbcDao.queryCount(sql);
		sql+=" order by con.create_time DESC";
		if(length!=null && length!=0) {
			sql+=" limit "+start+","+length;	
		}		
		List<Map<String, Object>> results = jdbcDao.queryForListMap(sql);
		result.setResultList(results);
		result.setTotalRows(count);
		return result;
		
	}
}
